from django.shortcuts import render, redirect, get_object_or_404
from django.contrib.auth.decorators import login_required
from django.contrib.auth import login, authenticate, logout
from django.contrib import messages
from django.http import JsonResponse
from django.db.models import Count, Sum, Case, When, IntegerField, F
from django.utils import timezone
from datetime import timedelta, datetime
import plotly.express as px
import plotly.graph_objects as go
import logging
from .models import Server, Task, TaskLog, SystemLog, Task, DatabaseConnection,QueryHistory
from .forms import CustomLoginForm, ServerForm, TaskForm,DatabaseConnectionForm,SQLQueryForm,StoredProcedureAnalysisForm
import re
import paramiko
import threading
import time
import uuid
from plotly.offline import plot
import cx_Oracle
import pymysql
import pyodbc
import re
from collections import defaultdict
#from .repositories import BusinessEfficiencyRepository

# 配置日志
logger = logging.getLogger(__name__)

def custom_login(request):
    """自定义登录视图"""
    return redirect('/admin/')
    if request.user.is_authenticated:
        return redirect('dashboard')
    
    if request.method == 'POST':
        form = CustomLoginForm(request, data=request.POST)
        if form.is_valid():
            username = form.cleaned_data.get('username')
            password = form.cleaned_data.get('password')
            user = authenticate(username=username, password=password)
            
            if user is not None:
                login(request, user)
                # 记录登录日志
                SystemLog.objects.create(
                    level='info',
                    message=f"用户 {username} 登录系统",
                    user=user,
                    ip_address=request.META.get('REMOTE_ADDR')
                )
                logger.info(f"用户 {username} 登录成功")
                return redirect('dashboard')
            else:
                messages.error(request, "用户名或密码不正确")
                # 记录登录失败日志
                SystemLog.objects.create(
                    level='warning',
                    message=f"用户 {username} 登录失败",
                    ip_address=request.META.get('REMOTE_ADDR')
                )
                logger.warning(f"用户 {username} 登录失败")
        else:
            messages.error(request, "登录表单无效，请检查输入")
    else:
        form = CustomLoginForm()
    
    return render(request, 'core/login.html', {'form': form})

def custom_logout(request):
    """自定义登出视图"""
    username = request.user.username
    logout(request)
    # 记录登出日志
    SystemLog.objects.create(
        level='info',
        message=f"用户 {username} 退出系统",
        ip_address=request.META.get('REMOTE_ADDR')
    )
    logger.info(f"用户 {username} 退出登录")
    messages.info(request, "您已成功登出")
    return redirect('admin')

@login_required
def dashboard(request):
    """仪表盘视图"""
    # 获取统计数据
    total_servers = Server.objects.count()
    total_tasks = Task.objects.count()
    running_tasks = Task.objects.filter(status='running').count()
    completed_tasks = Task.objects.filter(status='completed').count()
    failed_tasks = Task.objects.filter(status='failed').count()
    
    # 获取最近的任务和日志
    recent_tasks = Task.objects.all().order_by('-created_at')[:5]
    recent_logs = TaskLog.objects.all().order_by('-started_at')[:5]
    
    # 准备图表数据
    # 任务状态分布
    task_status_data = Task.objects.values('status').annotate(
        count=Count('id')
    ).order_by('status')
    
    status_labels = [item['status'] for item in task_status_data]
    status_counts = [item['count'] for item in task_status_data]
    
    status_fig = px.pie(
        values=status_counts,
        names=status_labels,
        title='任务状态分布',
        color_discrete_sequence=px.colors.qualitative.Pastel
    )
    status_fig.update_layout(
        autosize=True,
        height=300
    )
    status_chart = status_fig.to_html(full_html=False)
    
    # 服务器任务分布
    server_task_data = Server.objects.annotate(
        task_count=Count('tasks')
    ).values('name', 'task_count').order_by('-task_count')[:10]
    
    if server_task_data:
        server_fig = px.bar(
            server_task_data,
            x='name',
            y='task_count',
            title='服务器任务数量',
            labels={'name': '服务器', 'task_count': '任务数量'},
            color='task_count',
            color_continuous_scale='Viridis'
        )
        server_fig.update_layout(
            autosize=True,
            height=300
        )
        server_chart = server_fig.to_html(full_html=False)
    else:
        server_chart = None
    
    # 过去7天任务执行情况
    seven_days_ago = timezone.now() - timedelta(days=7)
    daily_task_data = TaskLog.objects.filter(
        started_at__gte=seven_days_ago
    ).extra({
        'date': "date(started_at)"
    }).values('date').annotate(
        total=Count('id'),
        completed=Sum(
            Case(When(status='completed', then=1), default=0, output_field=IntegerField())
        ),
        failed=Sum(
            Case(When(status='failed', then=1), default=0, output_field=IntegerField())
        )
    ).order_by('date')
    
    if daily_task_data:
        dates = [item['date'] for item in daily_task_data]
        totals = [item['total'] for item in daily_task_data]
        completed = [item['completed'] for item in daily_task_data]
        failed = [item['failed'] for item in daily_task_data]
        
        daily_fig = go.Figure()
        daily_fig.add_trace(go.Bar(
            x=dates, y=totals, name='总任务数', marker_color='lightblue'
        ))
        daily_fig.add_trace(go.Bar(
            x=dates, y=completed, name='完成任务', marker_color='lightgreen'
        ))
        daily_fig.add_trace(go.Bar(
            x=dates, y=failed, name='失败任务', marker_color='salmon'
        ))
        
        daily_fig.update_layout(
            title='过去7天任务执行情况',
            xaxis_title='日期',
            yaxis_title='任务数量',
            barmode='stack',
            autosize=True,
            height=300
        )
        daily_chart = daily_fig.to_html(full_html=False)
    else:
        daily_chart = None
    
    context = {
        'total_servers': total_servers,
        'total_tasks': total_tasks,
        'running_tasks': running_tasks,
        'completed_tasks': completed_tasks,
        'failed_tasks': failed_tasks,
        'recent_tasks': recent_tasks,
        'recent_logs': recent_logs,
        'status_chart': status_chart,
        'server_chart': server_chart,
        'daily_chart': daily_chart,
    }
    
    return render(request, 'core/dashboard.html', context)

# 服务器相关视图
@login_required
def server_list(request):
    """服务器列表视图"""
    servers = Server.objects.all().order_by('name')
    return render(request, 'core/server_list.html', {'servers': servers})

@login_required
def server_add(request):
    """添加服务器视图"""
    if request.method == 'POST':
        form = ServerForm(request.POST, user=request.user)
        if form.is_valid():
            server = form.save()
            # 记录日志
            SystemLog.objects.create(
                level='info',
                message=f"添加服务器: {server.name}",
                user=request.user,
                ip_address=request.META.get('REMOTE_ADDR')
            )
            logger.info(f"用户 {request.user.username} 添加了服务器 {server.name}")
            messages.success(request, f"服务器 {server.name} 添加成功")
            return redirect('server_list')
    else:
        form = ServerForm(user=request.user)
    
    return render(request, 'core/server_form.html', {'form': form, 'title': '添加服务器'})

@login_required
def server_edit(request, pk):
    """编辑服务器视图"""
    server = get_object_or_404(Server, pk=pk)
    
    if request.method == 'POST':
        form = ServerForm(request.POST, instance=server, user=request.user)
        if form.is_valid():
            updated_server = form.save()
            # 记录日志
            SystemLog.objects.create(
                level='info',
                message=f"更新服务器: {updated_server.name}",
                user=request.user,
                ip_address=request.META.get('REMOTE_ADDR')
            )
            logger.info(f"用户 {request.user.username} 更新了服务器 {updated_server.name}")
            messages.success(request, f"服务器 {updated_server.name} 更新成功")
            return redirect('server_list')
    else:
        form = ServerForm(instance=server, user=request.user)
    
    return render(request, 'core/server_form.html', {
        'form': form, 
        'title': f'编辑服务器: {server.name}',
        'server': server
    })

@login_required
def server_delete(request, pk):
    """删除服务器视图"""
    server = get_object_or_404(Server, pk=pk)
    server_name = server.name
    
    if request.method == 'POST':
        # 记录日志
        SystemLog.objects.create(
            level='info',
            message=f"删除服务器: {server_name}",
            user=request.user,
            ip_address=request.META.get('REMOTE_ADDR')
        )
        logger.info(f"用户 {request.user.username} 删除了服务器 {server_name}")
        
        server.delete()
        messages.success(request, f"服务器 {server_name} 已删除")
        return redirect('server_list')
    
    return render(request, 'core/server_confirm_delete.html', {'server': server})

@login_required
def test_server_connection(request, pk):
    """测试服务器连接"""
    server = get_object_or_404(Server, pk=pk)
    success, message = server.test_connection()
    
    # 记录测试结果
    log_level = 'info' if success else 'warning'
    SystemLog.objects.create(
        level=log_level,
        message=f"测试服务器 {server.name} 连接: {message}",
        user=request.user,
        ip_address=request.META.get('REMOTE_ADDR')
    )
    
    return JsonResponse({'success': success, 'message': message})

# 任务相关视图
@login_required
def task_list(request):
    """任务列表视图"""
    # tasks = Task.objects.all().order_by('-created_at')
    tasks = Task.objects.all().order_by('-created_at')  # 按创建时间倒序
    
    # 2. 为每个任务附加“最新日志”属性（核心预处理逻辑）
    for task in tasks:
        try:
            # 获取当前任务的最新日志（按 created_at 倒序，取第一条）
            # 注意：TaskLog 模型的时间字段是 created_at，不是 started_at
            latest_log = task.logs.latest('started_at')
            task.latest_log = latest_log  # 给任务添加自定义属性：latest_log
        except TaskLog.DoesNotExist:
            # 若任务无日志，设为 None（避免模板报错）
            task.latest_log = None
    
    # 3. 将处理后的 tasks 传递给模板
    return render(request, 'core/task_list.html', {'tasks': tasks})

@login_required
def task_add(request):
    """添加任务视图"""
    if request.method == 'POST':
        form = TaskForm(request.POST, user=request.user)
        if form.is_valid():
            task = form.save()
            # 记录日志
            SystemLog.objects.create(
                level='info',
                message=f"创建任务: {task.name}",
                user=request.user,
                ip_address=request.META.get('REMOTE_ADDR')
            )
            logger.info(f"用户 {request.user.username} 创建了任务 {task.name}")
            messages.success(request, f"任务 {task.name} 创建成功")
            return redirect('task_list')
    else:
        form = TaskForm(user=request.user)
    
    return render(request, 'core/task_form.html', {'form': form, 'title': '创建任务'})

@login_required
def task_edit(request, pk):
    """编辑任务视图"""
    task = get_object_or_404(Task, pk=pk)
    
    if request.method == 'POST':
        form = TaskForm(request.POST, instance=task, user=request.user)
        if form.is_valid():
            updated_task = form.save()
            # 记录日志
            SystemLog.objects.create(
                level='info',
                message=f"更新任务: {updated_task.name}",
                user=request.user,
                ip_address=request.META.get('REMOTE_ADDR')
            )
            logger.info(f"用户 {request.user.username} 更新了任务 {updated_task.name}")
            messages.success(request, f"任务 {updated_task.name} 更新成功")
            return redirect('task_list')
    else:
        form = TaskForm(instance=task, user=request.user)
    
    return render(request, 'core/task_form.html', {
        'form': form, 
        'title': f'编辑任务: {task.name}',
        'task': task
    })

@login_required
def task_delete(request, pk):
    """删除任务视图"""
    task = get_object_or_404(Task, pk=pk)
    task_name = task.name
    
    if request.method == 'POST':
        # 记录日志
        SystemLog.objects.create(
            level='info',
            message=f"删除任务: {task_name}",
            user=request.user,
            ip_address=request.META.get('REMOTE_ADDR')
        )
        logger.info(f"用户 {request.user.username} 删除了任务 {task_name}")
        
        task.delete()
        messages.success(request, f"任务 {task_name} 已删除")
        return redirect('task_list')
    
    return render(request, 'core/task_confirm_delete.html', {'task': task})

@login_required
def run_task(request, pk):
    """执行任务视图"""
    task = get_object_or_404(Task, pk=pk)
    
    if request.method == 'POST':
        # 执行任务
        success, message = task.run(request.user)
        
        # 记录日志
        log_level = 'info' if success else 'error'
        SystemLog.objects.create(
            level=log_level,
            message=f"执行任务 {task.name}: {message}",
            user=request.user,
            ip_address=request.META.get('REMOTE_ADDR')
        )
        
        if success:
            messages.success(request, f"任务 {task.name} 执行成功: {message}")
            logger.info(f"用户 {request.user.username} 成功执行任务 {task.name}")
        else:
            messages.error(request, f"任务 {task.name} 执行失败: {message}")
            logger.error(f"用户 {request.user.username} 执行任务 {task.name} 失败: {message}")
        
        return redirect('task_log', pk=task.logs.latest('started_at').pk)
    
    return render(request, 'core/task_confirm_run.html', {'task': task})

@login_required
def task_log(request, pk):
    """任务日志视图"""
    task_log = get_object_or_404(TaskLog, pk=pk)
    return render(request, 'core/task_log.html', {'task_log': task_log})

# 日志相关视图
@login_required
def log_list(request):
    """日志列表视图"""
    logs = TaskLog.objects.all().order_by('-started_at')
    return render(request, 'core/log_list.html', {'logs': logs})

@login_required
def log_detail(request, pk):
    """日志详情视图"""
    log = get_object_or_404(TaskLog, pk=pk)
    return render(request, 'core/log_detail.html', {'log': log})

# 报表相关视图
@login_required
def task_summary_report(request):
    """任务汇总报表"""
    # 任务状态统计
    status_data = Task.objects.values('status').annotate(
        count=Count('id')
    ).order_by('status')
    
    # 按服务器统计任务
    server_data = Server.objects.annotate(
        total_tasks=Count('tasks'),
        completed_tasks=Count(
            Case(When(tasks__status='completed', then=1), output_field=IntegerField())
        ),
        failed_tasks=Count(
            Case(When(tasks__status='failed', then=1), output_field=IntegerField())
        )
    ).values('name', 'total_tasks', 'completed_tasks', 'failed_tasks').order_by('-total_tasks')
    
    # 创建图表
    # 任务状态饼图
    if status_data:
        status_fig = px.pie(
            status_data,
            values='count',
            names='status',
            title='任务状态分布'
        )
        status_chart = status_fig.to_html(full_html=False)
    else:
        status_chart = None
    
    # 服务器任务柱状图
    if server_data:
        server_fig = go.Figure()
        server_fig.add_trace(go.Bar(
            x=[item['name'] for item in server_data],
            y=[item['completed_tasks'] for item in server_data],
            name='完成任务'
        ))
        server_fig.add_trace(go.Bar(
            x=[item['name'] for item in server_data],
            y=[item['failed_tasks'] for item in server_data],
            name='失败任务'
        ))
        server_fig.update_layout(
            title='服务器任务执行情况',
            barmode='stack'
        )
        server_chart = server_fig.to_html(full_html=False)
    else:
        server_chart = None
    
    context = {
        'status_data': status_data,
        'server_data': server_data,
        'status_chart': status_chart,
        'server_chart': server_chart,
    }
    
    return render(request, 'core/reports/task_summary.html', context)

@login_required
def server_activity_report(request):
    """服务器活动报表"""
    # 获取所有服务器
    servers = Server.objects.all()
    
    # 准备数据
    server_activity = []
    for server in servers:
        # 获取该服务器的任务数量
        task_count = server.tasks.count()
        
        # 获取该服务器的任务执行次数
        execution_count = TaskLog.objects.filter(task__server=server).count()
        
        # 获取成功和失败的执行次数
        success_count = TaskLog.objects.filter(
            task__server=server,
            status='completed'
        ).count()
        
        failed_count = TaskLog.objects.filter(
            task__server=server,
            status='failed'
        ).count()
        
        # 计算成功率
        success_rate = (success_count / execution_count) * 100 if execution_count > 0 else 0
        
        server_activity.append({
            'server': server,
            'task_count': task_count,
            'execution_count': execution_count,
            'success_count': success_count,
            'failed_count': failed_count,
            'success_rate': success_rate
        })
    
    # 按执行次数排序
    server_activity.sort(key=lambda x: x['execution_count'], reverse=True)
    
    # 创建图表
    if server_activity:
        # 服务器执行次数柱状图
        exec_fig = px.bar(
            server_activity,
            x=[item['server'].name for item in server_activity],
            y='execution_count',
            title='服务器任务执行次数',
            labels={'x': '服务器', 'execution_count': '执行次数'},
            color='execution_count',
            color_continuous_scale='Viridis'
        )
        exec_chart = exec_fig.to_html(full_html=False)
        
        # 成功率饼图
        success_rate_data = [
            {'name': item['server'].name, 'success_rate': item['success_rate']}
            for item in server_activity if item['execution_count'] > 0
        ]
        
        if success_rate_data:
            success_fig = px.bar(
                success_rate_data,
                x='name',
                y='success_rate',
                title='服务器任务成功率 (%)',
                labels={'name': '服务器', 'success_rate': '成功率 (%)'},
                color='success_rate',
                color_continuous_scale='RdYlGn'
            )
            success_fig.update_layout(yaxis_range=[0, 100])
            success_chart = success_fig.to_html(full_html=False)
        else:
            success_chart = None
    else:
        exec_chart = None
        success_chart = None
    
    context = {
        'server_activity': server_activity,
        'exec_chart': exec_chart,
        'success_chart': success_chart,
    }
    
    return render(request, 'core/reports/server_activity.html', context)


# 数据库管理模块视图
@login_required
def db_connection_list(request):
    """数据库连接配置列表"""
    connections = DatabaseConnection.objects.all()
    return render(request, 'core/db_connection_list.html', {'connections': connections})

@login_required
def db_connection_create(request):
    """创建数据库连接配置"""
    if request.method == 'POST':
        form = DatabaseConnectionForm(request.POST)
        if not form.is_valid():
            print("表单验证失败，错误信息：")
            print(form.errors)  # 打印所有错误（字段名: [错误信息列表]）
            print("表单数据：", form.data)  # 打印用户提交的原始数据，方便对比
        if form.is_valid():
            db_conn = form.save(commit=False)
            db_conn.created_by = request.user
            db_conn.save()
            messages.success(request, f'数据库连接 "{db_conn.name}" 创建成功！')
            return redirect('db_connection_list')
    else:
        form = DatabaseConnectionForm()
    
    return render(request, 'core/db_connection_form.html', {
        'form': form,
        'title': '创建数据库连接'
    })

@login_required
def db_connection_edit(request, pk):
    """编辑数据库连接配置"""
    db_conn = get_object_or_404(DatabaseConnection, pk=pk)
    
    if request.method == 'POST':
        form = DatabaseConnectionForm(request.POST, instance=db_conn)
        if form.is_valid():
            form.save()
            messages.success(request, f'数据库连接 "{db_conn.name}" 更新成功！')
            return redirect('db_connection_list')
    else:
        form = DatabaseConnectionForm(instance=db_conn)
    
    return render(request, 'core/db_connection_form.html', {
        'form': form,
        'title': '编辑数据库连接'
    })

@login_required
def db_connection_delete(request, pk):
    """删除数据库连接配置"""
    db_conn = get_object_or_404(DatabaseConnection, pk=pk)
    if request.method == 'POST':
        conn_name = db_conn.name
        db_conn.delete()
        messages.success(request, f'数据库连接 "{conn_name}" 已删除！')
        return redirect('db_connection_list')
    
    return render(request, 'core/db_connection_confirm_delete.html', {'connection': db_conn})

@login_required
def db_test_connection(request, pk):
    """测试数据库连接"""
    db_conn = get_object_or_404(DatabaseConnection, pk=pk)
    
    try:
        if db_conn.db_type == 'mysql':
            conn = pymysql.connect(
                host=db_conn.host,
                port=db_conn.port,
                user=db_conn.username,
                password=db_conn.password,
                db=db_conn.database_name,
                charset='utf8mb4',
                connect_timeout=10
            )
            conn.close()
            
        elif db_conn.db_type == 'sqlserver':
            conn_str = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={db_conn.host},{db_conn.port};"
            if db_conn.instance_name:
                conn_str += f"INSTANCE={db_conn.instance_name};"
            conn_str += f"DATABASE={db_conn.database_name};UID={db_conn.username};PWD={db_conn.password}"
            
            conn = pyodbc.connect(conn_str, timeout=10)
            conn.close()
            
        elif db_conn.db_type == 'oracle':
            dsn = cx_Oracle.makedsn(db_conn.host, db_conn.port, service_name=db_conn.instance_name)
            conn = cx_Oracle.connect(user=db_conn.username, password=db_conn.password, dsn=dsn, encoding="UTF-8", nencoding="UTF-8")
            
            # 关键：连接成功后，切换默认Schema为HR
            with conn.cursor() as cursor:
                # 执行切换Schema的SQL（HR为目标Schema名）
                cursor.execute("ALTER SESSION SET CURRENT_SCHEMA = "+db_conn.database_name)
            
            # 验证是否切换成功（可选）
            with conn.cursor() as cursor:
                cursor.execute("SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL")
                current_schema = cursor.fetchone()[0]
                print(f"已默认连接到Schema: {current_schema}")  # 应输出 HR
            
            conn.close()
            
        messages.success(request, f'数据库连接 "{db_conn.name}" 测试成功！')
    except Exception as e:
        messages.error(request, f'数据库连接 "{db_conn.name}" 测试失败: {str(e)}')
    
    return redirect('db_connection_list')

@login_required
def sql_query_test(request):
    """SQL查询测试功能"""
    form = SQLQueryForm(request.POST or None)
    results = None
    columns = None
    total_rows = 0
    page = int(request.GET.get('page', 1))
    query_id = request.GET.get('query_id')
    # 如果有查询ID，加载历史查询
    if query_id:
        try:
            query_history = QueryHistory.objects.get(id=query_id, created_by=request.user)
            form = SQLQueryForm(initial={
                'db_connection': query_history.db_connection,
                'sql': query_history.sql,
                'page_size': 50
            })
        except QueryHistory.DoesNotExist:
            messages.warning(request, '查询历史不存在或已被删除')
    
    # 执行查询
    if request.method == 'POST' and form.is_valid():
        db_conn = form.cleaned_data['db_connection']
        sql = form.cleaned_data['sql']
        page_size = form.cleaned_data['page_size']
        
        try:
            start_time = time.time()
            conn = None
            cursor = None
            
            # 根据数据库类型创建连接
            if db_conn.db_type == 'mysql':
                conn = pymysql.connect(
                    host=db_conn.host,
                    port=db_conn.port,
                    user=db_conn.username,
                    password=db_conn.password,
                    db=db_conn.database_name,
                    charset='utf8mb4'
                )
                cursor = conn.cursor(pymysql.cursors.DictCursor)
                
            elif db_conn.db_type == 'sqlserver':
                conn_str = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={db_conn.host},{db_conn.port};"
                if db_conn.instance_name:
                    conn_str += f"INSTANCE={db_conn.instance_name};"
                conn_str += f"DATABASE={db_conn.database_name};UID={db_conn.username};PWD={db_conn.password}"
                
                conn = pyodbc.connect(conn_str)
                cursor = conn.cursor()
                
            elif db_conn.db_type == 'oracle':
                dsn = cx_Oracle.makedsn(db_conn.host, db_conn.port, service_name=db_conn.instance_name)
                conn = cx_Oracle.connect(user=db_conn.username, password=db_conn.password, dsn=dsn, encoding="UTF-8", nencoding="UTF-8")
                
            
                # 关键：连接成功后，切换默认Schema为HR
                with conn.cursor() as cursor:
                    # 执行切换Schema的SQL（HR为目标Schema名）
                    cursor.execute("ALTER SESSION SET CURRENT_SCHEMA = "+db_conn.database_name)
                
                # 验证是否切换成功（可选）
                with conn.cursor() as cursor:
                    cursor.execute("SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL")
                    current_schema = cursor.fetchone()[0]
                    print(f"已默认连接到Schema: {current_schema}")  # 应输出 HR
                cursor = conn.cursor()

            if db_conn.db_type == 'oracle':
                cursor.execute("ALTER SESSION SET CURRENT_SCHEMA = "+db_conn.database_name)    
                # 获取总记录数（先执行COUNT查询）
                # count_sql = "ALTER SESSION SET CURRENT_SCHEMA = "+db_conn.database_name+";"+"SELECT COUNT(*) FROM ("+sql+") t"
                
                # 去除多行注释 /* ... */（包括跨多行的情况）
                sql = re.sub(r'/\*.*?\*/', '', sql, flags=re.DOTALL)
                
                # 去除单行注释 -- ...（直到行尾）
                sql = re.sub(r'--.*$', '', sql, flags=re.MULTILINE)
                
                # 去除所有分号（不仅是末尾，避免中间分号导致子查询提前结束）
                sql = sql.replace(';', '')
                
                # 去除首尾空白字符（空格、换行等）
                sql = sql.strip()

            elif db_conn.db_type == 'sqlserver':
                # 注意：保留注释中的换行符可能影响处理，用DOTALL模式匹配所有字符（包括\n）
                sql = re.sub(r'/\*.*?\*/', '', sql, flags=re.DOTALL)
                
                # 步骤2：去除单行注释 -- ...（直到行尾）
                # 保留换行符用于后续分号处理
                sql = re.sub(r'--.*$', '', sql, flags=re.MULTILINE)
                
                # 步骤3：处理分号（核心差异点）
                # SQL Server中，分号是语句分隔符，需保留语句内部必要的分号，但去除子查询末尾的分号
                # 方法：按分号分割语句，保留非空语句，最后拼接（避免末尾多余分号）
                statements = [s.strip() for s in sql.split(';') if s.strip()]
                sql = ';'.join(statements)  # 重新拼接，确保分号仅用于分隔有效语句
                
                # 步骤4：处理特殊语法（如CTE）
                # SQL Server中CTE（WITH ...）必须以分号开头（若前面有语句），但作为子查询时需调整
                # 若原始SQL以WITH开头，需确保前面无分号（避免子查询语法错误）
                if sql.strip().upper().startswith('WITH'):
                    sql = sql.lstrip(';').strip()  # 移除CTE前的分号
                
                # 步骤5：去除首尾空白
                sql = sql.strip()

            elif db_conn.db_type == 'mysql':
                # 步骤1：去除多行注释 /* ... */（包括嵌套注释，MySQL不支持嵌套但需兼容）
                # 使用非贪婪匹配，DOTALL模式匹配换行符
                sql = re.sub(r'/\*.*?\*/', '', sql, flags=re.DOTALL)
                
                # 步骤2：去除单行注释（# ... 和 --  ...）
                # 先处理#注释（#后直到行尾）
                sql = re.sub(r'#.*$', '', sql, flags=re.MULTILINE)
                # 再处理--注释（--后需加空格，否则可能是字段名的一部分，如--column）
                sql = re.sub(r'-- .*$', '', sql, flags=re.MULTILINE)
                
                # 步骤3：处理分号（移除所有分号，MySQL子查询中不允许分号）
                # 与SQL Server不同，MySQL中即使语句间的分号也会干扰子查询，直接全部移除
                sql = sql.replace(';', '')
                
                # 步骤4：处理反引号（可选，不影响语法但可规范）
                # MySQL常用反引号`包裹标识符，这里保留不处理（不影响子查询）
                
                # 步骤5：去除首尾空白字符
                sql = sql.strip()

            if db_conn.db_type == 'oracle':
                count_sql = "SELECT COUNT(*) FROM ("+sql+") t"
            elif db_conn.db_type == 'sqlserver':
                count_sql = "SELECT COUNT(*) FROM ("+sql+") AS t"
            elif db_conn.db_type == 'mysql':
                count_sql = "SELECT COUNT(*) FROM ("+sql+") t"

            cursor.execute(count_sql)
            total_rows = cursor.fetchone()[0]
            
            # 计算分页
            offset = (page - 1) * page_size
            
            # 根据数据库类型添加分页
            if db_conn.db_type == 'mysql':
                paginated_sql = f"{sql} LIMIT {page_size} OFFSET {offset}"
            elif db_conn.db_type == 'sqlserver':
                paginated_sql = f"{sql} OFFSET {offset} ROWS FETCH NEXT {page_size} ROWS ONLY"
            elif db_conn.db_type == 'oracle':
                paginated_sql = f"SELECT * FROM (SELECT t.*, ROWNUM rn FROM ({sql}) t WHERE ROWNUM <= {offset + page_size}) WHERE rn > {offset}"
            
            # 执行查询
            cursor.execute(paginated_sql)
            
            # 获取列名
            if db_conn.db_type == 'mysql':
                columns = [desc[0] for desc in cursor.description]
                results = cursor.fetchall()
            else:
                columns = [column[0] for column in cursor.description]
                # 转换结果为字典列表
                results = []
                for row in cursor.fetchall():
                    results.append(dict(zip(columns, row)))
            
            # 计算执行时间
            execution_time = time.time() - start_time
            
            # 保存查询历史
            query_history = QueryHistory(
                db_connection=db_conn,
                sql=sql,
                execution_time=execution_time,
                row_count=total_rows,
                created_by=request.user,
                is_success=True
            )
            query_history.save()
            
            messages.success(request, f'查询执行成功，返回 {total_rows} 条记录，耗时 {execution_time:.2f} 秒')
            
        except Exception as e:
            # 保存失败的查询历史
            query_history = QueryHistory(
                db_connection=db_conn,
                sql=sql,
                created_by=request.user,
                is_success=False,
                error_message=str(e)
            )
            query_history.save()
            print(e.args[0].message)
            messages.error(request, f'查询执行失败: {str(e)}')
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
    
    # 计算分页
    page_size = form.cleaned_data['page_size'] if form.is_valid() else 50
    total_pages = max(1, (total_rows + page_size - 1) // page_size)
    current_page = min(max(1, page), total_pages)
    
    # 获取查询历史
    query_history_list = QueryHistory.objects.filter(created_by=request.user).order_by('-created_at')[:10]
    
    context = {
        'form': form,
        'results': results,
        'columns': columns,
        'total_rows': total_rows,
        'total_pages': total_pages,
        'current_page': current_page,
        'query_history_list': query_history_list
    }
    
    return render(request, 'core/sql_query_test.html', context)

def extract_procedure_name(stored_procedure_code):
    # 优化正则：允许名称中包含 .（用于分隔schema、包、过程名）
    # 匹配 CREATE OR REPLACE PROCEDURE 后的完整名称（直到空格或括号结束）
    pattern = r'CREATE\s+OR\s+REPLACE\s+PROCEDURE\s+([\w\.]+)\s*'
    proc_name_match = re.search(pattern, stored_procedure_code, re.IGNORECASE)
    
    if not proc_name_match:
        return None  # 未匹配到存储过程定义
    
    full_name = proc_name_match.group(1).upper()  # 完整限定名（如 HR.MY_PACKAGE.GET_ORDER_INFO）
    
    # 可选：拆分出单独的过程名（最后一个 . 后的部分）
    proc_name_only = full_name.split('.')[-1]  # 如 GET_ORDER_INFO
    
    return {
        'full_name': full_name,         # 完整限定名（带schema和包名）
        'proc_name_only': proc_name_only  # 仅过程名（不含前缀）
    }

@login_required
def stored_procedure_analysis(request):
    """存储过程关联性分析（仅支持Oracle）"""
    form = StoredProcedureAnalysisForm(request.POST or None)
    analysis_results = None
    
    if request.method == 'POST' and form.is_valid():
        db_conn = form.cleaned_data['db_connection']
        stored_procedure_code = form.cleaned_data['stored_procedure']
        
        # 提取存储过程名称
        proc_name_match = re.search(r'CREATE\s+OR\s+REPLACE\s+PROCEDURE\s+(\w+)', stored_procedure_code, re.IGNORECASE)
        if not proc_name_match:
            messages.error(request, '无法从代码中提取存储过程名称，请检查输入')
            return render(request, 'core/stored_procedure_analysis.html', {'form': form})
        
        # proc_name = proc_name_match.group(1).upper()
        proc_name=extract_procedure_name(stored_procedure_code)
        try:
            # 连接Oracle数据库
            dsn = cx_Oracle.makedsn(db_conn.host, db_conn.port, service_name=db_conn.instance_name)
            conn = cx_Oracle.connect(user=db_conn.username, password=db_conn.password, dsn=dsn, encoding="UTF-8", nencoding="UTF-8")
            cursor = conn.cursor()
            
            # 初始化分析结果
            analysis_results = {
                'procedure_name': proc_name,
                'tables': {},
                'dependent_procedures': {},
                'dependent_functions': {}
            }
            
            # 1. 获取存储过程直接引用的表和视图
 
            
            # cursor.execute(" SELECT DISTINCT referenced_name, referenced_type  FROM user_dependencies WHERE name = '"++"'  AND type = 'PROCEDURE' AND referenced_type IN ('TABLE', 'VIEW')")
            cursor.execute("""
                SELECT DISTINCT referenced_name, referenced_type
                FROM user_dependencies
                WHERE name = :proc_name 
                AND type = 'PROCEDURE'
                AND referenced_type IN ('TABLE', 'VIEW')
            """, proc_name=proc_name['proc_name_only']) 

            tables_and_views = cursor.fetchall()
            
            # 2. 获取存储过程依赖的其他存储过程和函数
            cursor.execute("""
                SELECT DISTINCT referenced_name, referenced_type
                FROM user_dependencies
                WHERE name = :proc_name 
                  AND type = 'PROCEDURE'
                  AND referenced_type IN ('PROCEDURE', 'FUNCTION')
            """, {'proc_name': proc_name['proc_name_only']})
            
            dependent_objects = cursor.fetchall()
            
            # 3. 递归获取所有依赖的对象
            all_dependencies = set(dependent_objects)
            processed = set()
            
            while all_dependencies - processed:
                current = (all_dependencies - processed).pop()
                processed.add(current)
                
                cursor.execute("""
                    SELECT DISTINCT referenced_name, referenced_type
                    FROM user_dependencies
                    WHERE name = :obj_name 
                      AND type = :obj_type
                      AND referenced_type IN ('PROCEDURE', 'FUNCTION', 'TABLE', 'VIEW')
                """, {'obj_name': current[0], 'obj_type': current[1]})
                
                new_deps = cursor.fetchall()
                for dep in new_deps:
                    all_dependencies.add(dep)
            
            # 4. 收集所有涉及的表和视图
            all_tables_views = set(tables_and_views)
            for dep in all_dependencies:
                if dep[1] in ('TABLE', 'VIEW'):
                    all_tables_views.add(dep)
            
            # 5. 收集所有依赖的存储过程和函数
            for dep in all_dependencies:
                if dep[1] == 'PROCEDURE':
                    analysis_results['dependent_procedures'][dep[0]] = {
                        'type': 'PROCEDURE',
                        'code': None,
                        'tables': set()
                    }
                elif dep[1] == 'FUNCTION':
                    analysis_results['dependent_functions'][dep[0]] = {
                        'type': 'FUNCTION',
                        'code': None,
                        'tables': set()
                    }
            
            # 6. 分析每个表的详细信息
            for table_name, obj_type in all_tables_views:
                # 获取表结构DDL
                cursor.execute("SELECT DBMS_METADATA.GET_DDL(:obj_type, :name) FROM DUAL", 
                              {'obj_type': obj_type, 'name': table_name})
                ddl = cursor.fetchone()[0].read()
                
                # 获取索引信息
                cursor.execute("""
                    SELECT index_name, column_name, index_type
                    FROM user_ind_columns
                    WHERE table_name = :table_name
                    ORDER BY index_name, column_position
                """, {'table_name': table_name})
                indexes = defaultdict(list)
                for idx_row in cursor.fetchall():
                    indexes[idx_row[0]].append({
                        'column': idx_row[1],
                        'type': idx_row[2]
                    })
                
                # 获取触发器信息
                cursor.execute("""
                    SELECT trigger_name, trigger_type, triggering_event
                    FROM user_triggers
                    WHERE table_name = :table_name
                """, {'table_name': table_name})
                triggers = cursor.fetchall()
                
                # 获取触发器DDL
                trigger_ddls = []
                for trigger in triggers:
                    cursor.execute("SELECT DBMS_METADATA.GET_DDL('TRIGGER', :name) FROM DUAL", 
                                  {'name': trigger[0]})
                    trigger_ddl = cursor.fetchone()[0].read()
                    trigger_ddls.append({
                        'name': trigger[0],
                        'type': trigger[1],
                        'event': trigger[2],
                        'ddl': trigger_ddl
                    })
                
                # 获取数据量
                cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
                row_count = cursor.fetchone()[0]
                
                # 保存表信息
                analysis_results['tables'][table_name] = {
                    'type': obj_type,
                    'ddl': ddl,
                    'indexes': dict(indexes),
                    'triggers': trigger_ddls,
                    'row_count': row_count
                }
            
            # 7. 分析依赖的存储过程和函数
            for proc_name in analysis_results['dependent_procedures']:
                # 获取存储过程代码
                cursor.execute("SELECT DBMS_METADATA.GET_DDL('PROCEDURE', :name) FROM DUAL", 
                              {'name': proc_name})
                code = cursor.fetchone()[0].read()
                
                # 获取该存储过程引用的表
                cursor.execute("""
                    SELECT DISTINCT referenced_name
                    FROM user_dependencies
                    WHERE name = :proc_name 
                      AND type = 'PROCEDURE'
                      AND referenced_type IN ('TABLE', 'VIEW')
                """, {'proc_name': proc_name})
                
                tables_used = [row[0] for row in cursor.fetchall()]
                
                analysis_results['dependent_procedures'][proc_name]['code'] = code
                analysis_results['dependent_procedures'][proc_name]['tables'] = tables_used
            
            # 8. 分析依赖的函数
            for func_name in analysis_results['dependent_functions']:
                # 获取函数代码
                cursor.execute("SELECT DBMS_METADATA.GET_DDL('FUNCTION', :name) FROM DUAL", 
                              {'name': func_name})
                code = cursor.fetchone()[0].read()
                
                # 获取该函数引用的表
                cursor.execute("""
                    SELECT DISTINCT referenced_name
                    FROM user_dependencies
                    WHERE name = :func_name 
                      AND type = 'FUNCTION'
                      AND referenced_type IN ('TABLE', 'VIEW')
                """, {'func_name': func_name})
                
                tables_used = [row[0] for row in cursor.fetchall()]
                
                analysis_results['dependent_functions'][func_name]['code'] = code
                analysis_results['dependent_functions'][func_name]['tables'] = tables_used
            
            messages.success(request, f'存储过程 "{proc_name}" 的关联性分析完成')
            
        except Exception as e:
            messages.error(request, f'分析过程中发生错误: {str(e)}')
        finally:
            if 'cursor' in locals() and cursor:
                cursor.close()
            if 'conn' in locals() and conn:
                conn.close()
    
    context = {
        'form': form,
        'analysis_results': analysis_results
    }
    
    return render(request, 'core/stored_procedure_analysis.html', context)